﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace QLBV
{
    public partial class QLBN : System.Web.UI.Page
    {

        String strCon = "Data Source=HTD-COM;Initial Catalog=QLBV;Integrated Security=True;Pooling=False";
        SqlConnection conn;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["Dang nhap"] == "1")
            {
                txtBS.Text = Session["User"].ToString();
            }
            Load_Table();
        }
        public void Load_Table()
        {
            lbMsg.Text = "";
            String strSelect = "SELECT * FROM BenhNhan";
            conn = new SqlConnection(strCon);
            conn.Open();
           SqlDataAdapter  adapter = new SqlDataAdapter(strSelect, conn);
           DataTable  dt = new DataTable();
            adapter.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
            conn.Close();
        }
        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

            String strBN="Select * from BenhNhan where MaBN='"+GridView1.SelectedRow.Cells[1].Text+"'";
            String strTN = "Select * from ThanNhan where MaTN='" + GridView1.SelectedRow.Cells[7].Text + "'";
            conn.Open();
            SqlDataAdapter da=new SqlDataAdapter(strBN,conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "BenhNhan");
            da = new SqlDataAdapter(strTN, conn);
            da.Fill(ds, "ThanNhan"); 
           //Benh nhan
            txtMaBN.Text = GridView1.SelectedRow.Cells[1].Text;
            txtName.Text = ds.Tables["BenhNhan"].Rows[0][1].ToString();
           txtAddressBN.Text = ds.Tables["BenhNhan"].Rows[0][2].ToString();
            txtPhone.Text = ds.Tables["BenhNhan"].Rows[0][3].ToString();
            txtMarital.Text = ds.Tables["BenhNhan"].Rows[0][4].ToString();
            if (ds.Tables["BenhNhan"].Rows[0][7].ToString().Trim() =="Nam")
            {
                listGender.SelectedIndex = 0;
            }
            else listGender.SelectedIndex = 1;
            txtCanBenh.Text = ds.Tables["BenhNhan"].Rows[0][5].ToString();
            //Than nhan
            txtNameTN.Text = ds.Tables["ThanNhan"].Rows[0][0].ToString();
            txtAddressTN.Text = ds.Tables["ThanNhan"].Rows[0][1].ToString();
            txtPhoneTN.Text = ds.Tables["ThanNhan"].Rows[0][2].ToString();
            txtQH.Text = ds.Tables["ThanNhan"].Rows[0][3].ToString();
            conn.Close();
        }

        protected void btInsert_Click(object sender, EventArgs e)
        {
            String strInsertBN="INSERT INTO BenhNhan	(HoTen, DiaChi,SDT,HonNhan,CanBenh,MaTN,GioiTinh )VALUES ( @hoten,@diachi,@sdt,@honnhan,@canbenh,@matn,@gioitinh)";
            String strInsertTN = "INSERT INTO ThanNhan	(HoTen, DiaChi,SDT,QuanHe )VALUES ( @hoten,@diachi,@sdt,@quanhe)";
            String MaTN="";
            if (txtAddressBN.Text == "" ||
          txtMarital.Text == "" ||
          txtPhone.Text == "" ||
          txtCanBenh.Text == "" ||
          txtName.Text == "" ||
        txtNameTN.Text == "" ||
          txtNameTN.Text == "" ||
          txtQH.Text == "" ||
          txtAddressTN.Text == "")
            {
                lbMsg.Text = "Vui lòng nhập đầy đủ thông tin ";
            }
            else 
                if (check_phone(txtPhone.Text, txtPhoneTN.Text, txtMaBN.Text) == false)
                lbMsg.Text="Số đt này đã tồn tại!";
                else
                    {
                        lbMsg.Text = "";
                    conn.Open();
                    //insert than nhan
                    SqlCommand cmd = new SqlCommand(strInsertTN,conn);
                    cmd.Parameters.Add("@hoten",txtNameTN.Text);
                    cmd.Parameters.Add("@diachi", txtAddressTN.Text);
                    cmd.Parameters.Add("@sdt", txtPhoneTN.Text);
                    cmd.Parameters.Add("@quanhe", txtQH.Text);
                    cmd.ExecuteNonQuery();
                    //Lay ma than nhan
                     SqlDataAdapter adapter = new SqlDataAdapter("Select * from ThanNhan where SDT= '"+txtPhoneTN.Text+"'",conn);
                     DataTable dt = new DataTable();
                     adapter.Fill(dt);
                     if (dt.Rows.Count > 0)
                     {
                         MaTN = dt.Rows[0][4].ToString();
                         //insert benh nhan
                         SqlCommand cmdBN = new SqlCommand(strInsertBN, conn);
                         cmdBN.Parameters.Add("@hoten", txtName.Text);
                         cmdBN.Parameters.Add("@diachi", txtAddressBN.Text);
                         cmdBN.Parameters.Add("@sdt", txtPhone.Text);
                         cmdBN.Parameters.Add("@honnhan", txtMarital.Text);
                         cmdBN.Parameters.Add("@canbenh", txtCanBenh.Text);
                         cmdBN.Parameters.Add("@matn", MaTN);
                         cmdBN.Parameters.Add("@gioitinh", listGender.Text);
                         cmdBN.ExecuteNonQuery();
                         conn.Close();
                         Load_Table();
                     }
                     else conn.Close();
                     reset();
                        }
            
        }

        protected void txtAddressTN_TextChanged(object sender, EventArgs e)
        {

        }

        protected void btUpdate_Click(object sender, EventArgs e)
        {
            lbMsg.Text = "";
            String MaTN="";
            String strUpdate = "UPDATE BenhNhan SET  HoTen=@hoten, DiaChi=@diachi,SDT=@sdt,HonNhan=@honnhan,CanBenh=@canbenh,GioiTinh=@gioitinh WHERE MaBN = '"+txtMaBN.Text+"'";
            if (txtAddressBN.Text == "" ||
          txtMarital.Text == "" ||
          txtPhone.Text == "" ||
          txtCanBenh.Text == "" ||
          txtName.Text == "" ||
        txtNameTN.Text == "" ||
          txtNameTN.Text == "" ||
          txtQH.Text == "" ||
          txtAddressTN.Text == "")
            {
                lbMsg.Text = "Vui lòng nhập đầy đủ thông tin ";
            }
            else
            {
                if (check_phone(txtPhone.Text, txtPhoneTN.Text, txtMaBN.Text) == false) lbMsg.Text = "SDT đã tồn tại!";
                else
                {
                    conn.Open();
                    //update benh nhan
                    SqlCommand cmdBN = new SqlCommand(strUpdate, conn);
                    cmdBN.Parameters.Add("@hoten", txtName.Text);
                    cmdBN.Parameters.Add("@diachi", txtAddressBN.Text);
                    cmdBN.Parameters.Add("@sdt", txtPhone.Text);
                    cmdBN.Parameters.Add("@honnhan", txtMarital.Text);
                    cmdBN.Parameters.Add("@canbenh", txtCanBenh.Text);
                    cmdBN.Parameters.Add("@gioitinh", listGender.Text);
                    cmdBN.ExecuteNonQuery();
                    //update than nhanh
                    SqlDataAdapter adapter = new SqlDataAdapter("Select MaTN from BenhNhan where MaBN= '" + txtMaBN.Text + "'", conn);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        MaTN = dt.Rows[0][0].ToString();
                        String strUpdateTN = "UPDATE ThanNhan SET  HoTen=@hoten, DiaChi=@diachi,SDT=@sdt,QuanHe=@quanhe WHERE MaTN = '" + MaTN + "'";
                        SqlCommand cmdTN = new SqlCommand(strUpdateTN, conn);
                        cmdTN.Parameters.Add("@hoten", txtNameTN.Text);
                        cmdTN.Parameters.Add("@diachi", txtAddressTN.Text);
                        cmdTN.Parameters.Add("@sdt", txtPhoneTN.Text);
                        cmdTN.Parameters.Add("@quanhe", txtQH.Text);
                        cmdTN.ExecuteNonQuery();
                    }
                    conn.Close();
                    Load_Table();
                    reset();
                }
            }
        }

        protected void btDelete_Click(object sender, EventArgs e)
        {
            lbMsg.Text = "";
            String MaTN = "";
            String strDeleteBN = "DELETE FROM BenhNhan WHERE MaBN = '" +txtMaBN.Text +"'";
            conn.Open();
            SqlCommand cmdBN = new SqlCommand(strDeleteBN,conn);
            SqlDataAdapter adapter = new SqlDataAdapter("Select MaTN from BenhNhan where MaBN= '" + txtMaBN.Text + "'", conn);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                MaTN = dt.Rows[0][0].ToString();

            }
            String strDeleteTN = "DELETE FROM ThanNhan WHERE MaTN = '" + MaTN + "'";
            SqlCommand cmdTN = new SqlCommand(strDeleteTN, conn);
            cmdBN.ExecuteNonQuery();
            cmdTN.ExecuteNonQuery();            
            conn.Close();
            Load_Table();
            reset();
        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            GridView1.DataBind();
        }

        protected void btReset_Click(object sender, EventArgs e)
        {
            reset();
        }

        private void reset()
        {
            lbMsg.Text = "";
            //Benh nhan
            txtAddressBN.Text = "";
            txtMaBN.Text = "";
            txtMarital.Text = "";
            txtPhone.Text = "";
            txtCanBenh.Text = "";
            listGender.Text = "";
            txtName.Text = "";
            //Than nhan
            txtNameTN.Text = "";
            txtPhoneTN.Text = "";
            txtQH.Text = "";
            txtAddressTN.Text = "";
        }
       private Boolean check_phone(String phoneBN,String phoneTN, String maBN)
        {
            String maTN = "";
           //get ma Than Nhan
            String ma = "Select MaTN from BenhNhan where MaBN='" + maBN + "'";
            conn.Open();
            SqlCommand cmd = new SqlCommand(ma, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            maTN=reader[0].ToString();
                reader.Close();

            //check phone BN
            String strPhoneBN = "Select SDT from BenhNhan where SDT='" + phoneBN + "' and MaBN!='" + maBN + "'";
            SqlCommand cmdBN = new SqlCommand(strPhoneBN, conn);
            SqlDataReader readerBN = cmdBN.ExecuteReader();
            if (readerBN.Read())
            {
                readerBN.Close();
                return false;
            }
              readerBN.Close();
            //check phone TN
            String strPhoneTN = "Select SDT from ThanNhan where SDT='" + phoneTN + "' and MaTN!='" + maTN + "'";
            SqlCommand cmdTN = new SqlCommand(strPhoneTN, conn);
            SqlDataReader readerTN = cmdTN.ExecuteReader();
            if (readerTN.Read())
            {
                readerTN.Close();
                conn.Close();
                return false;
            }
            readerTN.Close();
            conn.Close();
            return true;
        }       
    }
}